﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.WebControls;
using GWEB.WebBase.Data;
using GWEB.WebBase.Entity;
using GWEB.WebBase.Utility;
using GWEB.WebFrame.Public;

namespace GWEB.WebFrame.Schedule
{
    public class CourseController : PageInfoBase
    {
        int _timeId;
        int _roomId;
        String leagueId;
        String userId;

        [ActionFilter(IsLogin = true)]
        public ActionResult Index(String id, String keyCode, int timeId, int roomId)
        {
            DataRow drUser = BasePage.GetUser(Session);
            leagueId = Fun.cString(drUser["LeagueId"]);
            userId = Fun.cString(drUser["iuserid"]);
            _timeId = timeId;
            _roomId = roomId;
            //String keyCode = Fun.IDCode(id);

            String sql = String.Format(@"delete cu_TeacherCourse_temp where userId={0}", userId);
            BasePage.bd.ExecuteSQL(sql);

            sql = String.Format("select id,name from cu_Course_class where LeagueId={0} and state=1 ", leagueId);
            ViewBag.CourseClass = BasePage.bd.ExecuteDictArray(sql);
            ViewBag.ClassRoomId = 0;
            sql = String.Format("select iuserid id,sshowname name from tb_sys_users where usertype=2 and LeagueId={0} ", leagueId);
            ViewBag.Teacher = BasePage.bd.ExecuteDictArray(sql);

            ViewBag.Guid = Guid.NewGuid().ToString("N");
            if (id != "-1")
            {
                sql = String.Format(@"insert into cu_TeacherCourse_temp(guid,cid,signupClass,userId) select '{0}',cid,signupClass,{2} from cu_TeacherCourse_sign where tid={1}", ViewBag.Guid, id, userId);
                BasePage.bd.ExecuteSQL(sql);
            }
            return Load("Course", "Schedule/Course", id, keyCode);
        }

        protected override void FieldAddInit(DataRow data)
        {
            //教室
            String sql = String.Format("select id,name from cu_ClassRoom where LeagueId={0} and state=1 and id={1}", leagueId, _roomId);
            DataRow dr = BasePage.bd.ExecuteRow(sql);
            if (dr != null)
            {
                data["ClassRoomId"] = dr["id"];
                data["ClassRoomName"] = dr["name"];
            }
            //时间
            sql = String.Format("select id,startTime,endTime from cu_time where LeagueId={0} and state=1 and id={1}", leagueId, _timeId);
            dr = BasePage.bd.ExecuteRow(sql);
            if (dr != null)
            {
                data["timeId"] = dr["id"];
                data["startTime"] = dr["startTime"];
                data["endTime"] = dr["endTime"];
            }
            data["TeachDate"] = DateTime.Now.ToString("yyyy-MM-dd");
            ViewBag.week = Convert.ToDateTime(data["TeachDate"].ToString()).DayOfWeek;
            base.FieldAddInit(data);
        }
        protected override void FieldEditInit(DataRow data)
        {
            ViewBag.week = Convert.ToDateTime(data["TeachDate"].ToString()).DayOfWeek;
            //data["TeachDate"] = Convert.ToDateTime(data["techdate"].ToString()).ToString("yyyy-MM-dd");
            String sql = String.Format("select classId from cu_Course where id={0}", data["CourseId"].ToString());
            ViewBag.ClassRoomId = BasePage.bd.ExecuteScalarInt(sql);
            base.FieldEditInit(data);
        }


        String startDate;
        String endDate;
        String guid;
        protected override string Validate(Dictionary<string, object> data)
        {
            String message = "";
            message = base.Validate(data); if (!message.Equals("")) return message;

            String teachDate = Fun.cSql(data["teachDate"]);
            startDate = teachDate.Substring(0, 10);
            endDate = teachDate.Substring(teachDate.Length - 10);
            if (startDate.CompareTo(DateTime.Now.ToString("yyyy-MM-dd")) == -1)
                return "不能增加或修改以前日期的排课";
            if (Convert.ToDateTime(endDate).Subtract(Convert.ToDateTime(startDate)).Days > 365)
                return "两个时间间隔不能超过1年";

            guid = Fun.cSql(data["guid"]);

            String sql = String.Format(@"select
    isnull(sum(case when SignupClass=0 then 1 else 0 end),0) s0,
	isnull(sum(case when SignupClass=1 then 1 else 0 end),0) s1
from cu_TeacherCourse_temp where guid = '{0}'", guid);
            DataRow dr = BasePage.bd.ExecuteRow(sql);
            if (dr == null) return "数据异常";
            if (int.Parse(dr["s0"].ToString()) > int.Parse(data["studentNumber"].ToString()))
                return "选择的上课学员超过学员人数限制";
            if (int.Parse(dr["s1"].ToString()) > int.Parse(data["LineUpNumber"].ToString()))
                return "选择的排队学员超过可排人数限制";
            return "";
        }
        //执行排课
        protected override SqlState SqlExecute(BaseData bd, string id, Dictionary<string, object> data)
        {
            DataRow drUser = BasePage.GetUser(Session);
            leagueId = Fun.cString(drUser["LeagueId"]);
            userId = Fun.cString(drUser["iuserid"]);

            String timeId = Fun.cSql(data["timeId"]);
            String classRoomId = Fun.cSql(data["classRoomId"]);
            String courseId = Fun.cSql(data["courseId"]);
            String className = Fun.cSql(data["className"]);
            String studentNumber = Fun.cSql(data["studentNumber"]);
            String teacherId = Fun.cSql(data["teacherId"]);
            String twoTeacherId = Fun.cSql(data["twoTeacherId"]);
            String lineUpNumber = Fun.cSql(data["LineUpNumber"]);
            String week = Fun.cSql(data["week"]);
            String color = Fun.cSql(data["color"]);


            String[] sqls = new string[5];
            //当天及以后的课删除
            sqls[0] = String.Format(@"delete cu_TeacherCourse where teachDate in (
    select date from
    (
        select
            convert(datetime, '{0}') + id - 1 date,
            datepart(weekday, convert(datetime, '{0}') + id - 1 - 1) week
        from tb_sys_id
    ) t1
    where date between '{0}' and '{1}'
    and week in ({2})
) and TimeId = {3} and ClassRoomId = {4} and TeachDate>= convert(varchar(10), getdate(), 120) and LeagueId={5}", startDate, endDate, week, timeId, classRoomId, leagueId);
            //插入课程表
            sqls[1] = String.Format(@"insert into cu_TeacherCourse
(ClassRoomId, ClassRoomName, StudentNumber, LineUpNumber, TeachDate, TimeId, startTime, endTime,
CourseId, CourseName, ClassName,
TeacherId, TeacherName, TwoTeacherId, TwoTeacherName, Color,
CreateId, Createdate, state, LeagueId,guid
)
select
    t2.id, t2.name, {8}, {9}, date, t3.id, t3.startTime, t3.endTime,
    t4.id, t4.CourseName, '{10}',
    t5.iuserid, t5.sshowname, t6.iuserid, t6.sshowname, '{11}',
    {12}, getdate(), 0, {13},'{14}'
from
    (
        select
            convert(datetime, '{0}') + id - 1 date,
            datepart(weekday, convert(datetime, '{0}') + id - 1 - 1) week
        from tb_sys_id
    ) t1
    inner join cu_ClassRoom t2 on t2.id = {4}
    inner join cu_Time t3 on t3.id = {3}
    inner join cu_course t4 on t4.id = {5}
    inner join tb_sys_users t5 on t5.iuserid = {6}
    left join tb_sys_users t6 on t6.iuserid = {7}
    where date between '{0}' and '{1}'
    and week in ({2})", startDate, endDate, week, timeId, classRoomId, courseId, teacherId, twoTeacherId,
    studentNumber, lineUpNumber, className, color,
    userId, leagueId, guid);
            //插入上课及排除人员
            sqls[2] = String.Format(@"insert into cu_TeacherCourse_sign
(
	Cid,Tid,Pnumber,CreateId,Createdate,
    state,IsAuditions,SignupClass,LeagueId,ContractId,AchievementId,AchievementName,isMakeup
)
select 
	t1.cid,t2.id,1,{2},getdate(),
	0,'R',SignupClass,{1},isnull(t3.ContractId,  (select top 1  cu_Contract.id  from cu_Contract
	left join 
	cu_Contract_Class on cu_Contract_Class.id=cu_Contract.SetmealId
	where (
	(IsClassHour=0 and  isnull(CourseEnd,'2099-12-31')>GETDATE()) or 
	(IsClassHour=1 and  isnull(CourseEnd,'2099-12-31')>GETDATE() and  isnull(SurplusNumber,0)-isnull(GiveCourseNumber,0)>0)
	) 
	and  status in (1,2,3,4) and cu_Contract.state='2'   and cu_Contract.Cid=t1.cid)) ContractId,t3.LatelyReceId,t4.sshowname,0
from cu_TeacherCourse_temp t1
inner join cu_TeacherCourse t2 on t1.guid=t2.guid
inner join cu_customers t3 on t1.cid=t3.id
left join tb_sys_users t4 on t3.LatelyReceId=t4.iuserid
where t1.guid='{0}'", guid, leagueId, userId);
            sqls[3] = string.Format(@"update cu_Contract set CourseStart=GETDATE(),CourseEnd=DATEADD(DAY,DATEDIFF(day,constart,conend),getdate()),status=3 from 
(select   min(cu_Contract.id) id,cid  from cu_Contract
	left join 
	cu_Contract_Class on cu_Contract_Class.id=cu_Contract.SetmealId
	where (
	(IsClassHour=0 and  isnull(CourseEnd,'2099-12-31')>GETDATE()) or 
	(IsClassHour=1 and  isnull(CourseEnd,'2099-12-31')>GETDATE() and  isnull(SurplusNumber,0)-isnull(GiveCourseNumber,0)>0)
	) 
	and  status in (1,2,3,4) and cu_Contract.state='2' group by cid
	
	) t1 
	inner join  cu_TeacherCourse_temp t2  on t1.Cid=t2.cid 
	inner join cu_Customers t3 on t3.id=t2.cid
	where t3.ContractId is null  and t3.id=cu_Contract.Cid  and t2.guid='{0}' ", guid);
            sqls[4] = string.Format(@"update cu_Customers set ContractId=t2.id from cu_TeacherCourse_temp t1 inner join (select   min(cu_Contract.id) id,cid  from cu_Contract

    left join

    cu_Contract_Class on cu_Contract_Class.id = cu_Contract.SetmealId

    where(
    (IsClassHour = 0 and  isnull(CourseEnd, '2099-12-31') > GETDATE()) or
         (IsClassHour = 1 and  isnull(CourseEnd, '2099-12-31') > GETDATE() and  isnull(SurplusNumber, 0) - isnull(GiveCourseNumber, 0) > 0)
	) 
	and status in (1, 2, 3, 4) and cu_Contract.state = '2' group by cid) t2 on t2.Cid = t1.cid

    where t1.cid = cu_customers.id and cu_Customers.ContractId is null and  t1.guid='{0}'  ", guid);

            bool t = BasePage.bd.ExecuteSQL(sqls);
            return t ? SqlState.True : SqlState.False;
        }

        //获取分类下的课程
        [ActionFilter(IsJson = true)]
        public JsonResult CourseData(int id)
        {
            DataRow drUser = BasePage.GetUser(Session);
            leagueId = Fun.cString(drUser["LeagueId"]);
            String sql = String.Format(@"select id,coursename,stunumber from cu_Course where LeagueId={0} and state=1 and classId={1} ", leagueId, id);
            return BasePage.Json(ResultData.OK(BasePage.bd.ExecuteDictArray(sql)));
        }
        //获取学生
        [ActionFilter(IsJson = true)]
        public JsonResult StudentData(String guid)
        {
            guid = Fun.cSql(guid);
            String sql = String.Format(@"select t1.id,custName,datediff(m,BirdDate,getdate()) custMonth,linkName,custTel,
    case when signupclass=0 then '正常' else '排队' end signupclass
from cu_customers t1
inner join cu_TeacherCourse_Temp t2 on t1.id=t2.cid
where t2.guid='{0}' ", guid);
            return BasePage.Json(ResultData.OK(BasePage.bd.ExecuteDictArray(sql)));
        }
        //退课
        [ActionFilter(IsJson = true)]
        public JsonResult DelStudent(String guid, int id)
        {
            guid = Fun.cSql(guid);
            String sql = String.Format(@"delete cu_TeacherCourse_Temp where guid='{0}' and cid={1}", guid, id);
            bool t = BasePage.bd.ExecuteSQL(sql);
            return BasePage.Json(t ? ResultData.OK("退课成功") : ResultData.Error("退课失败"));
        }
        //将小组加入课程【cu_TeacherCourse_Temp临时存储表】
        [ActionFilter(IsJson = true)]
        public JsonResult AddToCourse(String id, String keyCode, String guid, String courseId)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Error(WebMessage.NoGrant));
            DataRow drUser = BasePage.GetUser(Session);
            userId = Fun.cString(drUser["iuserid"]);
            String act = "";
            if (courseId != "null")
            {
                act = "and (select count(*) from cu_customers  where id=cid and datediff(m,BirdDate,getdate())>=(select StartMonth from cu_Course where id ={3}) and datediff(m,BirdDate,getdate())<=(select EndMonth from cu_Course where id ={3}))>0";
            }
            String sql = String.Format(@"insert into cu_TeacherCourse_Temp(guid,cid,SignupClass,userId)
select distinct '{1}',cid,0,{2} from cu_cus_group t1 
inner join cu_Cus_Group_Detail t2 on t1.id=t2.GroupId where t1.id={0} and cid not in (select cid from cu_TeacherCourse_Temp where guid='{1}')
" + act, id, guid, userId, courseId);
            bool t = BasePage.bd.ExecuteSQL2(sql);
            return BasePage.Json(t ? ResultData.OK("加入成功") : ResultData.Error("加入失败"));
        }


        //将学员加入课程【cu_TeacherCourse_Temp临时存储表】
        [ActionFilter(IsJson = true)]
        public JsonResult AddToCourseByStudent(String id, String keyCode, String guid, int signupClass)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Error(WebMessage.NoGrant));
            DataRow drUser = BasePage.GetUser(Session);
            userId = Fun.cString(drUser["iuserid"]);

            String sql = String.Format(@"insert into cu_TeacherCourse_Temp(guid,cid,SignupClass,userId)
select '{1}',id,{2},{3} from cu_Customers t1
where t1.id={0} and id not in (select cid from cu_TeacherCourse_Temp where guid='{1}')", id, guid, signupClass, userId);
            bool t = BasePage.bd.ExecuteSQL2(sql);
            return BasePage.Json(t ? ResultData.OK("加入成功") : ResultData.Error("加入失败"));
        }
    }
}